using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Collections.Generic;
using System.Collections;
using Sql.DataAccessLayer;
/// <summary>
/// Summary description for CategoriesBLL
/// </summary>
public class CategoriesBLL
{   
	public CategoriesBLL()
	{
		
	}
    // Note: categories Important Terms

    // Type:
    // ........... 0: Videos.
    // ........... 1: Groups
    // ........... 2: Channels
    // ........... 3: General // represent all types
    // ........... 4: Abuse Types
    // ........... 5: Photos, Galleries
    // ........... 6: Blogs
    // ........... 7: Audio Files
    // ........... 8: Galleries
    // ........... 11: QA
    // ........... 12: Forum
    // ........... 13: Classified
    // ........... 14: Website Directories

    // isprivate
    //............ 0: public
    //............ 1: protected, private
    //............ 2: all

    // Mode (It is used to group certain categories in order to display in separate sections) e.g group auto categoreis via car brands, auto shows, etc
    // .............. 0: Normal - No Filter (All Categories)
   
    // ParentID -/ Normal parent child category management.
    
    
    // Core section for preparing and loading n-tier parent / child categories.
    #region CategorySection
    private static string Indicator = ""; // set child level indicator start with empty "", "-", "--", "---", etc

    public static void BindCategories(ListControl cat, int ParentId, int type,string defaulttxt,string defaultvalue,bool iscategoryid)
    {
        cat.Items.Clear();
        ListItem _list = new ListItem();
        //_list.Text = " " + Resources.vsk.selectcategory + " ";
        _list.Text = defaulttxt;
        // _list.Value = " ";
        _list.Value = defaultvalue;
        cat.Items.Add(_list);
        // Recursion Starts '//
        Process_Category_Items(cat, 0, type,iscategoryid);
    }

    // Recursive function -> Target Dropdownlist
    public static void Process_Category_Items(ListControl cat, int parentid, int type,bool iscategoryid)
    {
        // iscategoryid = true will load dropdown values as categoryid otherwise term or categoryname
        int isprivate = 0; // load only public categories
        int mode = 0; // normal category - no filter
        // load all categories based on parentid
        List<Category_Struct_V2> _list = CategoriesBLL.Load_CategoriesSummary(parentid, type, true,isprivate,mode,"categoryname asc",0,1);
        int i = 0;
        ListItem _lst;
        string _name;
        if (_list.Count > 0)
        {
            for (i = 0; i < _list.Count; i++)
            {
                if (parentid == 0)
                    Indicator = "";

                if (Indicator != "")
                    _name = Indicator + "" + _list[i].CategoryName;
                else
                    _name = _list[i].CategoryName;

                // _lst = new ListItem(_name, _list[i].CategoryID.ToString()); // value based on categoryid
                 string _term = "";
                 if (iscategoryid)
                     _term = _list[i].CategoryID.ToString();
                 else
                 {
                     _term = _list[i].Term;
                     if (_term == "")
                         _term = _list[i].CategoryName;
                 }
                _lst = new ListItem(_name, _term); // value based on term
                cat.Items.Add(_lst);

                if (CategoriesBLL.Has_Child(_list[i].CategoryID,isprivate))
                {
                    Indicator = Indicator + "- ";
                    Process_Category_Items(cat, _list[i].CategoryID, type,iscategoryid);
                }
            }

            if (Indicator.Trim() != "")
                Indicator = Indicator.Remove(Indicator.LastIndexOf("- "));

            Indicator = Indicator + " ";
        }
    }

    // Recursive fuction -> Target Listing
    public static void Process_Category_List_Items(ArrayList _arr, int parentid, int type, bool isall, int total_records,string order,int pagenumber)
    {

        int isprivate = 0; // load only public categories
        int mode = 0; // normal category - no filter
        // load all categories based on parentid
        List<Category_Struct_V2> _list = CategoriesBLL.Load_CategoriesSummary(parentid, type, isall, isprivate,mode,order,total_records,pagenumber);
        if (_list.Count > 0)
        {
            int i = 0;
            string _name;
            for (i = 0; i < _list.Count; i++)
            {
                if (parentid == 0)
                    Indicator = "";

                if (Indicator != "")
                    _name = Indicator + "" + _list[i].CategoryName;
                else
                    _name = _list[i].CategoryName;

                Category_Struct _category_struct = new Category_Struct(_list[i].CategoryID, _name, _list[i].Term, parentid, Indicator);
                _arr.Add(_category_struct);
                if (!isall && _arr.Count >= total_records)
                    return; // break processing
                if (CategoriesBLL.Has_Child(_list[i].CategoryID, isprivate))
                {
                    //Indicator = Indicator + "- ";
                    Process_Category_List_Items(_arr, _list[i].CategoryID, type, isall,total_records,order,pagenumber);
                }
            }

            if (Indicator.Trim() != "")
                Indicator = Indicator.Remove(Indicator.LastIndexOf("- "));

            Indicator = Indicator + " ";
        }
    }
    
    public static void Process_Category_List_Items_Extended(ArrayList _arr, int parentid, int type, bool isall,int records, int mode,string order,int pagenumber)
    {
        int isprivate = 0; // load only public categories
        // load all categories based on parentid
        List<Category_Struct_V2> _list = CategoriesBLL.Load_CategoriesSummary(parentid, type, isall, isprivate, mode,order,records,pagenumber);
        if (_list.Count > 0)
        {
            int i = 0;
            string _name;
            for (i = 0; i < _list.Count; i++)
            {

                if (parentid == 0)
                    Indicator = "";

                if (Indicator != "")
                    _name = Indicator + "" + _list[i].CategoryName;
                else
                    _name = _list[i].CategoryName;

                Category_Struct _category_struct = new Category_Struct(_list[i].CategoryID, _name,_list[i].Term, parentid, _list[i].Priority, _list[i].Type, Indicator, _list[0].isPrivate);
                _arr.Add(_category_struct);
                if (!isall && _arr.Count >= records)
                    return; // break processing
                if (CategoriesBLL.Has_Child(_list[i].CategoryID, isprivate))
                {
                    //Indicator = Indicator + "- ";
                    Process_Category_List_Items_Extended(_arr, _list[i].CategoryID, type, isall, records, mode, order, pagenumber);
                }
            }

            if (Indicator.Trim() != "")
                Indicator = Indicator.Remove(Indicator.LastIndexOf("- "));

            Indicator = Indicator + " ";

        }
    }
     
    #endregion

   
    public static bool Process(Category_Struct_V2 cat, bool isUpdate)
    {
        if(!isUpdate)
            SqlHelper.ExecuteNonQuery(Config.ConnectionString, CommandType.Text, "Insert Into categories(CategoryName,ParentID,Added_Date,Type,Priority,isprivate,mode,term,picturename,description)values(@CategoryName,@ParentID,@Added_Date,@type,@Priority,@isprivate,@mode,@term,@picturename,@description)", new SqlParameter("@CategoryName", cat.CategoryName), new SqlParameter("@ParentID", cat.ParentID), new SqlParameter("@Added_Date", DateTime.Now), new SqlParameter("@Type", cat.Type), new SqlParameter("@Priority", cat.Priority), new SqlParameter("@isprivate", cat.isPrivate), new SqlParameter("@mode", cat.Mode),new SqlParameter("@term",cat.Term),new SqlParameter("@picturename",cat.PictureName),new SqlParameter("@description",cat.Description));
        else
            SqlHelper.ExecuteNonQuery(Config.ConnectionString, CommandType.Text, "Update categories set CategoryName=@CategoryName,ParentID=@ParentID,Type=@Type,Priority=@Priority,isPrivate=@isPrivate,mode=@mode,term=@term,picturename=@picturename,description=@description WHERE CategoryID=@CategoryID", new SqlParameter("@CategoryName", cat.CategoryName), new SqlParameter("@ParentID", cat.ParentID), new SqlParameter("@CategoryID", cat.CategoryID), new SqlParameter("@Type", cat.Type), new SqlParameter("@Priority", cat.Priority), new SqlParameter("@isPrivate", cat.isPrivate), new SqlParameter("@mode", cat.Mode), new SqlParameter("@term", cat.Term), new SqlParameter("@picturename", cat.PictureName), new SqlParameter("@description", cat.Description));
        return true;
    }
   
    public static bool Delete(int CategoryID)
    {
        SqlHelper.ExecuteNonQuery(Config.ConnectionString,CommandType.Text,"Delete from categories WHERE CategoryID=" + CategoryID);
        return true;
    }

    public static bool Delete(int CategoryID, int AssignCategoryID)
    {
        SqlHelper.ExecuteNonQuery(Config.ConnectionString, CommandType.Text, "Delete from categories WHERE CategoryID=" + CategoryID);

        // Assign all contents of deleted category with assigned caetgory
        Update_Category(CategoryID, AssignCategoryID, "videos");
        Update_Category(CategoryID, AssignCategoryID, "galleries");
        Update_Category(CategoryID, AssignCategoryID, "photos");
        Update_Category(CategoryID, AssignCategoryID, "blogs");
        Update_Category(CategoryID, AssignCategoryID, "groups");
        Update_Category(CategoryID, AssignCategoryID, "users");
        return true;
    }

    // assign all contents to another category
    public static bool Update_Category(int categoryid, int assigncategoryid, string tablename)
    {
        if (tablename == "users")
            SqlHelper.ExecuteNonQuery(Config.ConnectionString, CommandType.Text, "Update " + tablename + " set AccountType=" + assigncategoryid + " WHERE AccountType=" + categoryid);
        else
            SqlHelper.ExecuteNonQuery(Config.ConnectionString, CommandType.Text, "Update " + tablename + " set categoryid=" + assigncategoryid + " WHERE categoryid=" + categoryid);
        return true;
    }
        
    public static List<Category_Struct_V2> Load_Categories(string search,int categoryid, int parentid, int type,int isprivate, string order)
    {
        SqlConnection con = new SqlConnection(Config.ConnectionString);
        con.Open();
        List<Category_Struct_V2> _items = new List<Category_Struct_V2>();
        Category_Struct_V2 _item;

        // generate sql query
        StringBuilder str = new StringBuilder();
        if (categoryid > 0)
        {
            // load single record based on categoryid
            str.Append("SELECT * from categories where categoryid=" + categoryid);
        }
        else
        {
            string _isprivate = "";
            if (isprivate != 2)
                _isprivate = " AND isprivate=" + isprivate + "";
            string _search = "";
            if (search != "")
                _search = " AND (categoryname like '%" + search + "%' OR term like '%" + search + "%')";
            str.Append("SELECT * from categories where parentid=" + parentid + "" + _search + "" + _isprivate + " AND type=" + type + " order by " + order);
        }
       
         // execute sql query
        SqlCommand video_cmd = new SqlCommand(str.ToString(), con);
        SqlDataReader reader = video_cmd.ExecuteReader(CommandBehavior.CloseConnection);
        while (reader.Read())
        {
            _item = new Category_Struct_V2();
            _item.CategoryID = Convert.ToInt32(reader["CategoryID"]);
            _item.CategoryName = reader["CategoryName"].ToString();
            _item.ParentID = Convert.ToInt32(reader["ParentID"]);
            _item.Type = Convert.ToInt32(reader["Type"]);
            _item.Priority = Convert.ToInt32(reader["Priority"]);
            _item.isPrivate = Convert.ToInt32(reader["isprivate"]);
            _item.Term = reader["term"].ToString();
            _item.PictureName = reader["picturename"].ToString();
            _item.Description = reader["description"].ToString();
            _item.Mode = Convert.ToInt32(reader["mode"]);
            _items.Add(_item);
        }
        reader.Close();
        con.Close();

        return _items;
    }

    public static List<Category_Struct_V2> Load_CategoriesSummary(int CategoryID, int type, bool isAll, int isprivate,int mode,string order,int records, int pagenumber)
    {

        SqlConnection con = new SqlConnection(Config.ConnectionString);
        con.Open();
        List<Category_Struct_V2> _items = new List<Category_Struct_V2>();
        Category_Struct_V2 _item;

        string _isprivate = "";
        if (isprivate != 2)
            _isprivate = " AND isprivate=" + isprivate + "";

        // generate sql query
        StringBuilder str = new StringBuilder();
        StringBuilder query = new StringBuilder();
        string _mode = "";
        if(mode >0)
            _mode =" AND Mode=" + mode;

        string _parentid = " AND ParentID=" + CategoryID + "";
        if (isAll)
            str.Append("SELECT CategoryID,CategoryName,Term,Records from categories WHERE (type = " + type + " OR type=3) " + _parentid + " " + _isprivate + "" + _mode + " ORDER By " + order + "");
        else
        {
            query.Append("CategoryID,CategoryName,Term,Records from categories WHERE (type = " + type + " OR type=3) " + _parentid + " " + _isprivate + "" + _mode + "");
            int paginationtype = 0;
            switch (paginationtype)
            {
                case 0:
                    // SQL SERVER 2005 or Later Supported Query
                    str.Append(Pagination_Process.Prepare_SQLSERVER2005_Pagination(query.ToString(), order, pagenumber, records));
                    break;
                case 1:
                    // MySQL Supported Query
                    string mysql_query = "SELECT " + query.ToString() + " ORDER BY " + order;
                    str.Append(Pagination_Process.Prepare_MySQL_Pagination(mysql_query, pagenumber, records));
                    break;
                case 2:
                    // SQL SERVER 2000 Supported Query
                    string normal_query = "SELECT " + query.ToString() + " ORDER BY " + order;
                    str.Append(normal_query);
                    break;
            }
        }
           
        // execute sql query
        SqlCommand video_cmd = new SqlCommand(str.ToString(), con);
        SqlDataReader reader = video_cmd.ExecuteReader(CommandBehavior.CloseConnection);
        while (reader.Read())
        {
            _item = new Category_Struct_V2();
            _item.CategoryID = Convert.ToInt32(reader["CategoryID"]);
            _item.Term = reader["Term"].ToString();
            _item.CategoryName = reader["CategoryName"].ToString();
            _item.Records = Convert.ToInt32(reader["records"]);
            _items.Add(_item);
        }
        reader.Close();
        con.Close();

        return _items;
    }

    // Count Categories
    public static int Count_CategoriesSummary(int type, int isprivate, int mode)
    {
        string _isprivate = "";
        if (isprivate != 2)
            _isprivate = " AND isprivate=" + isprivate + "";

        // generate sql query
        StringBuilder str = new StringBuilder();
        string _mode = "";
        if (mode > 0)
            _mode = " AND Mode=" + mode;

        str.Append("SELECT Count(CategoryID) from categories WHERE (type = " + type + " OR type=3) " + _isprivate + "" + _mode + "");
      
        return Convert.ToInt32(SqlHelper.ExecuteScalar(Config.ConnectionString,CommandType.Text,str.ToString()));
    }
             
    public static bool Has_Child(int categoryid,int isprivate)
    {

        string _isprivate = "";
        if (isprivate != 2)
            _isprivate = " AND isprivate=" + isprivate + "";

        int result = Convert.ToInt32(SqlHelper.ExecuteScalar(Config.ConnectionString, CommandType.Text, "SELECT Count(CategoryID) from categories WHERE ParentID=" + categoryid + "" + _isprivate + ""));
        if(result == 0)
            return false;
        else
            return true;
    
    }

    public static int Count(int parentid, int type, int isprivate)
    {
        string _isprivate = "";
        if (isprivate != 2)
            _isprivate = " AND isprivate=" + isprivate + "";

        return Convert.ToInt32(SqlHelper.ExecuteScalar(Config.ConnectionString, CommandType.Text, "SELECT Count(CategoryID) from categories WHERE parentid=" + parentid + " AND Type=" + type + "" + _isprivate + ""));
    }

    public static int Count(int type,int isprivate)
    {
        string _isprivate = "";
        if (isprivate != 2)
            _isprivate = " AND isprivate=" + isprivate + "";

        return Convert.ToInt32(SqlHelper.ExecuteScalar(Config.ConnectionString, CommandType.Text, "SELECT Count(CategoryID) from categories WHERE Type=" + type + "" + _isprivate + "") );
    }
     
    // get category id
    public static int Return_CategoryID(int type, string categoryname)
    {
        return Convert.ToInt32(SqlHelper.ExecuteScalar(Config.ConnectionString, CommandType.Text, "SELECT categoryid from categories WHERE type=" + type + " AND (categoryname='" + categoryname + "' OR term='" + categoryname + "')"));
    }
  
}
